package cn.bestick.cblog.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import cn.bestick.cblog.dao.MsgDao;
import cn.bestick.cblog.dmo.Msg;
import cn.bestick.cblog.dto.MsgDto;
import cn.bestick.cblog.dto.MsgReplyDto;
import cn.bestick.cblog.dto.UserDto;
import cn.bestick.cblog.util.DateUtil;

public class MsgDaoImpl extends BaseDao implements MsgDao {

	private Connection con = null;

	private List<MsgReplyDto> getChirdren(Connection con, int type, int pid)
			throws Exception {
		con = getCon();
		List<MsgReplyDto> msgReplyDtos = new ArrayList<MsgReplyDto>();
		String sql = "SELECT * FROM t_msg , t_user WHERE t_msg.userId = t_user.userId AND t_msg.type = ? AND t_msg.pid = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, type);
		pstmt.setInt(2, pid);
		ResultSet rs = pstmt.executeQuery();
		while (rs.next()) {
			MsgReplyDto msgReplyDto = new MsgReplyDto();
			msgReplyDto.setId(rs.getInt("msgId"));
			msgReplyDto.setPid(rs.getInt("pid"));
			msgReplyDto.setPubDate(DateUtil.formatString(
					rs.getString("pubDate"), "yyyy-MM-dd HH:mm:ss"));
			msgReplyDto.setContent(rs.getString("content"));
			UserDto userDto = new UserDto();
			userDto.setId(rs.getInt("userId"));
			userDto.setImageName(rs.getString("imageName"));
			userDto.setNickname(rs.getString("nickname"));
			userDto.setWebsite(rs.getString("website"));
			msgReplyDto.setUser(userDto);

			msgReplyDtos.add(msgReplyDto);
		}
		close(con);
		return msgReplyDtos;
	}

	public List<MsgDto> getComments(int articleId) throws Exception {
		con = getCon();
		List<MsgDto> msgDtos = new ArrayList<MsgDto>();
		// 1.查找article下的msg
		String sql = "SELECT * FROM t_msg , t_user WHERE t_msg.userId = t_user.userId AND t_msg.type = 0 AND t_msg.pid = ?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, articleId);
		ResultSet rs = pstmt.executeQuery();
		while (rs.next()) {
			MsgDto msgDto = new MsgDto();
			msgDto.setId(rs.getInt("msgId"));
			msgDto.setPid(rs.getInt("pid"));
			msgDto.setType(rs.getInt("type"));
			msgDto.setReplyType(2);
			msgDto.setPubDate(DateUtil.formatString(rs.getString("pubDate"),
					"yyyy-MM-dd HH:mm:ss"));
			msgDto.setContent(rs.getString("content"));
			UserDto userDto = new UserDto();
			userDto.setId(rs.getInt("userId"));
			userDto.setImageName(rs.getString("imageName"));
			userDto.setNickname(rs.getString("nickname"));
			userDto.setWebsite(rs.getString("website"));
			msgDto.setUser(userDto);

			// 2.查找msg的子msg
			msgDto.setChirdren(this.getChirdren(con, msgDto.getReplyType(),
					msgDto.getId()));

			msgDtos.add(msgDto);
		}
		close(con);
		return msgDtos;
	}

	public List<MsgDto> getLeaveMsgs() throws Exception {
		con = getCon();
		List<MsgDto> msgDtos = new ArrayList<MsgDto>();
		// 1.查找article下的msg
		String sql = "SELECT * FROM t_msg , t_user WHERE t_msg.userId = t_user.userId AND t_msg.type = 1 order by pubDate desc ";
		PreparedStatement pstmt = con.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		while (rs.next()) {
			MsgDto msgDto = new MsgDto();
			msgDto.setId(rs.getInt("msgId"));
			msgDto.setPid(rs.getInt("pid"));
			msgDto.setType(rs.getInt("type"));
			msgDto.setReplyType(3);
			msgDto.setPubDate(DateUtil.formatString(rs.getString("pubDate"),
					"yyyy-MM-dd HH:mm:ss"));
			msgDto.setContent(rs.getString("content"));
			UserDto userDto = new UserDto();
			userDto.setId(rs.getInt("userId"));
			userDto.setImageName(rs.getString("imageName"));
			userDto.setNickname(rs.getString("nickname"));
			userDto.setWebsite(rs.getString("website"));
			msgDto.setUser(userDto);

			// 2.查找msg的子msg
			msgDto.setChirdren(this.getChirdren(con, msgDto.getReplyType(),
					msgDto.getId()));

			msgDtos.add(msgDto);
		}
		close(con);
		return msgDtos;
	}

	public void save(Msg msg) throws Exception {
		con = getCon();

		String sql = "insert into t_msg values(null,?,?,?,?,now(),0)";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, msg.getUserId());
		pstmt.setInt(2, msg.getType());
		pstmt.setInt(3, msg.getPid());
		pstmt.setString(4, msg.getContent());
		pstmt.executeUpdate();

		close(con);
	}

	public void delete(int id) throws Exception {
		con = getCon();

		String sql = "delete from t_msg where msgId=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, id);
		pstmt.executeUpdate();

		close(con);
	}

	@Override
	public List<MsgDto> getRecentMsgs() throws Exception {
		con = getCon();
		String sql = "select * from t_msg,t_user where t_msg.userId = t_user.userId order by pubDate desc limit 1,10";
		PreparedStatement pstmt = con.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		List<MsgDto> msgs = new ArrayList<MsgDto>();
		while (rs.next()) {
			MsgDto msg = new MsgDto();
			UserDto user = new UserDto();
			msg.setContent(rs.getString("content"));
			user.setNickname(rs.getString("nickname"));
			user.setWebsite(rs.getString("website"));
			msg.setUser(user);
			msgs.add(msg);
		}
		close(con);
		return msgs;
	}

	@Override
	public List<MsgDto> all() throws Exception {
		con = getCon();
		String sql = "select * from t_msg,t_user where t_msg.userId = t_user.userId order by pubDate desc";
		PreparedStatement pstmt = con.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		List<MsgDto> msgs = new ArrayList<MsgDto>();
		while (rs.next()) {
			MsgDto msg = new MsgDto();
			UserDto user = new UserDto();
			msg.setId(rs.getInt("msgId"));
			msg.setType(rs.getInt("type"));
			msg.setPid(rs.getInt("pid"));
			msg.setPubDate(rs.getDate("pubDate"));
			msg.setContent(rs.getString("content"));
			user.setNickname(rs.getString("nickname"));
			user.setWebsite(rs.getString("website"));
			msg.setUser(user);
			msgs.add(msg);
		}
		close(con);
		return msgs;
	}

}
